Skip Headers

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 23 Managing Automatic System Tasks Using the Maintenance Window

Oracle Database is preconfigured to perform some routine database maintenance tasks so that you can run them at times when the system load is expected to be light. You can specify for such a time period a resource plan that controls the resource consumption of those maintenance tasks. When the designated time period ends, the database can switch to a different resource plan that lowers the resource allocation for any remaining maintenance tasks.

This chapter consists of the following sections:

- Maintenance Windows

- Automatic Statistics Collection Job

- Resource Management

## Maintenance Windows

The Oracle Scheduler enables you to create time windows during which jobs are automatically run. A typical Scheduler window defines a start time, a duration, and optionally a resource plan to activate. A Scheduler job can then name a window as its schedule. (When the window "opens," the job begins to run.) In addition, windows can be combined into window groups, and if a job names a window group as its schedule instead of naming a window, the job runs whenever any of the windows in the window group opens.

Two Scheduler windows are predefined upon installation of Oracle Database:

- `WEEKNIGHT_WINDOW` starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.

- `WEEKEND_WINDOW` covers whole days Saturday and Sunday.

Together these windows constitute the `MAINTENANCE_WINDOW_GROUP` in which all system maintenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection and for some other internal system maintenance jobs. If you are using the Resource Manager, you can also assign resource plans to these windows.

You can adjust the predefined maintenance windows to a time suitable to your database environment using the `DBMS_SCHEDULER.SET_ATTRIBUTE` procedure. For example, the following script moves the `WEEKNIGHT_WINDOW` to midnight to 8 a.m. every weekday morning:

    EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(
       'WEEKNIGHT_WINDOW',
       'repeat_interval',
       'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

You can also use the `SET_ATTRIBUTE` procedure to adjust any other property of a window. For example, the following script sets resource plan `DEFAULT_MAINTENANCE_PLAN` for the `WEEKNIGHT_WINDOW`:

    EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE (
       'WEEKNIGHT_WINDOW',
       'resource_plan',
       'DEFAULT_MAINTENANCE_PLAN');

In this case, if you have already enabled a different resource plan, Oracle Database will make the `DEFAULT_MAINTENANCE_PLAN` active when the `WEEKNIGHT_WINDOW` opens, and will reactivate the original resource plan when the `WEEKNIGHT_WINDOW` closes.

See Also: Chapter 27, "Using the Scheduler" for more information on the Scheduler, and PL/SQL Packages and Types Reference for information on the `DBMS_SCHEDULER` package

## Automatic Statistics Collection Job

A Scheduler program `GATHER_STATS_PROG` and Scheduler job `GATHER_STATS_JOB` are predefined on installation of Oracle Database. `GATHER_STATS_PROG` collects optimizer statistics for all objects in the database for which there are no statistics or only stale statistics. `GATHER_STATS_JOB` is defined on `GATHER_STATS_PROG` and is scheduled to run in the `MAINTENANCE_WINDOW_GROUP`.

If you prefer to manage statistics collection manually, you can disable the job as follows:

    EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

See Also: Oracle Database Performance Tuning Guide for more information on automatic statistics collection

## Resource Management

A Resource Manager consumer group, `AUTO_TASK_CONSUMER_GROUP`, is predefined on installation of Oracle Database, and a Scheduler job class `AUTO_TASKS_JOB_CLASS` is defined based on this consumer group. The `GATHER_STATS_JOB` is defined to run in the `AUTO_TASKS_JOB_CLASS` job class.

When a resource plan is activated in the system, `GATHER_STATS_JOB` and any internal system tasks conform to the resource consumption specified for `AUTO_TASK_CONSUMER_GROUP` in this resource plan.

See Also: Chapter 24, "Using the Database Resource Manager" for more information about creating and modifying resource plans.